package local.xinshi.netmgr.pkg;

import java.time.Duration;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.PostConstruct;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service
public class SqlService {

    @Autowired
    SqliteDao baseDao;

    /**
     * @return String 用户角色,用户不存在返回null,密码错误返回空字符串
     * @param name 用户名
     * @param pwd  密码
     */
    public String getUserRole(String name, String pwd) {
        List<String[]> list = baseDao.query("select `pwd`,`role` from cfg_users where name=?", 2, name);
        if (list != null && list.size() != 0) {
            // 用户是唯一的,所以list必定只有一个元素:{"pwd","role"}
            String[] record = list.get(0);
            return record[0].equals(pwd) ? record[1] : "";
        }
        return null;
    }

    /**
     * 查询参数值
     * 
     * @param name 要查询的参数名,pascal命名法
     * @return 参数值
     */
    public String getVal(String name) {
        String sql = "select val from cfg_args where name=?";
        return baseDao.query(sql, 1, name).get(0)[0];
    }

    /**
     * 更新数据表
     * 
     * @param sql  执行的语句
     * @param args 占位符参数值
     * @return boolean 更新成功返回true
     */
    public boolean updateTb(String sql, String... args) {
        return baseDao.execute(sql, args) > 0;
    }

    /**
     * 初始化数据库，创建表cfg_users、cfg_room、cfg_args，并在cfg_users插入管理员：root,密码：root
     * 在cfg_args表中插入默认的ACL名称、SSH主机地址和SSH用户等信息
     */
    public void initDb() {
        baseDao.execute("drop table if exists cfg_users;");
        baseDao.execute("drop table if exists cfg_room;");
        baseDao.execute("drop table if exists cfg_args");
        baseDao.execute("drop table if exists logs");
        baseDao.execute("CREATE TABLE cfg_users(name text primary key,pwd text not null,role text not null);");
        baseDao.execute("CREATE TABLE cfg_room(name text primary key,iface text not null,itype text not null);");
        baseDao.execute("CREATE TABLE cfg_args(name text primary key,val text not null,desc text null);");
        baseDao.execute("CREATE TABLE logs(timestamp text not null,sid text not null,msg text not null);");
        baseDao.execute("insert into cfg_users values(?,?,?);", "root", "root", "管理员");
        baseDao.execute("insert into cfg_args values(?,?,?)", "SshHost", "localhost", "SSH目标地址");
        baseDao.execute("insert into cfg_args values(?,?,?)", "SshUser", "admin", "SSH用户名");
        baseDao.execute("insert into cfg_args values(?,?,?)", "SshPwd", "admin", "SSH用户密码");
        baseDao.execute("insert into cfg_args values(?,?,?)", "PermitAcl", "permit", "允许流量的ACL名称");
        baseDao.execute("insert into cfg_args values(?,?,?)", "DenyAcl", "deny", "拒绝流量的ACL名称");
        baseDao.execute("insert into cfg_args values(?,?,?)", "AutoDeny", "13:00,20:00", "自动关闭网络的时间,多个时间点以英文逗号'',''分隔");
        baseDao.execute("insert into cfg_args values(?,?,?)", "EnableAuto", "1", "是否自动关闭网络: 1是  0否");
        baseDao.execute("insert into cfg_args values(?,?,?)", "PortGroup", "class", "交换机接口组名称");
    }

    /**
     * 实例化后检查数据库的cfg_users表是否存在管理员，如果不存在或出现异常则初始化数据库
     */
    @PostConstruct
    private void init() {
        List<String[]> list = baseDao.query("select role from cfg_users where role='管理员';", 0);
        if (list == null || list.size() == 0)
            initDb();
    }

    /**
     * 从指定数据表获取指定字段的所有数据
     * 
     * @param tb   指定的数据表名称
     * @param or   是否要根据指定字段排序,如果传入null或''则不排序
     * @param cols 指定的字段,多参数或数组
     * @return JSON数组格式的字符串，数组元素是JSON对象：{col1:?,col2:?.....}
     */
    public List<Map<String, String>> listTable(String tb, String or, String... cols) {
        StringBuilder sb = new StringBuilder("select ");
        for (int i = 0; i < cols.length; i++) {
            sb.append(cols[i] + ",");
        }
        sb.deleteCharAt(sb.length() - 1).append(" from " + tb);
        if (or != null && "".equals(or)) {
            sb.append(" order by " + or);
        }
        List<String[]> list = baseDao.query(sb.toString(), cols.length);
        List<Map<String, String>> result = new ArrayList<>();
        list.forEach(e -> {
            Map<String, String> map = new HashMap<>();
            for (int i = 0; i < cols.length; i++) {
                map.put(cols[i], e[i]);
            }
            result.add(map);
        });
        return result;
    }

    /**
     * 查询房间和交换机接口对应关系并返回映射:{room:itypeiface}
     */
    public Map<String, String> getRoom() {
        String sql = "select * from cfg_room";
        List<String[]> list = baseDao.query(sql, 3);
        Map<String, String> map = new HashMap<>();
        list.forEach(e -> {
            map.put(e[0], e[2] + e[1]);
        });
        return map;
    }

    /**
     * 记录日志
     * 
     * @param sid session ID
     * @param msg 日志消息
     */
    public void log(String sid, String msg) {
        String cutime = Instant.now().toEpochMilli() + "";
        baseDao.execute("insert into logs values(?,?,?)", cutime, sid, msg);
    }

    /**
     * 查看日志
     * 
     * @param pgNo 分页偏移页数
     * @return List&lt;Map&lt;String,String&gt;&gt 每个map有两个元素：一个时间戳，一个操作记录
     */
    public List<Map<String, String>> getLogs(int pgNo) {
        String sql = "select * from `logs` order by `timestamp` desc limit 100 offset " + pgNo * 100;
        List<String[]> list = baseDao.query(sql, 3);
        List<Map<String, String>> result = new ArrayList<>();
        Map<String, List<Map<String, String>>> resultMap = new LinkedHashMap<>();
        list.forEach(e -> {
            List<Map<String, String>> temp;
            Map<String, String> map = new HashMap<>();
            map.put("time", LocalDateTime.ofInstant(Instant.ofEpochMilli(Long.valueOf(e[0])), ZoneId.systemDefault())
                    .format(DateTimeFormatter.ofPattern("MM-dd HH:mm:ss")));
            map.put("action", e[2]);
            if ((temp = resultMap.get(e[1])) == null) {
                temp = new ArrayList<>();
                resultMap.put(e[1], temp);
            }
            temp.add(map);
        });
        resultMap.forEach((s, l) -> {
            l.sort((e1, e2) -> {
                return e1.get("time").compareTo(e2.get("time"));
            });
            result.addAll(l);
        });
        return result;
    }

    /**
     * 清除一周前的操作记录
     */
    public void cleanLog() {
        baseDao.execute("delete from logs where `timestamp` < ?",
                Instant.now().minusMillis(Duration.ofDays(7).toMillis()).toEpochMilli() + "");
    }

}